<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>JavaWeb🌍数据库技术 | 小李博客</title><meta name="keywords" content="JavaWeb"><meta name="author" content="小李博客"><meta name="copyright" content="小李博客"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="JDBC简介 JDBC全称是Java数据库连接（Java Database Connectivity），它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据的查询、更新、新增和删除的操作     应用程序使用JDBC访问特定的数据库时，需要与不同的数据库驱动进行连接。由于不同数据库厂商提供的数据库驱动不同，因此，为了使应用程序">
<meta property="og:type" content="article">
<meta property="og:title" content="JavaWeb🌍数据库技术">
<meta property="og:url" content="http://xiaoliblog.cn/page/JavaWeb09.html">
<meta property="og:site_name" content="小李博客">
<meta property="og:description" content="JDBC简介 JDBC全称是Java数据库连接（Java Database Connectivity），它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据的查询、更新、新增和删除的操作     应用程序使用JDBC访问特定的数据库时，需要与不同的数据库驱动进行连接。由于不同数据库厂商提供的数据库驱动不同，因此，为了使应用程序">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png">
<meta property="article:published_time" content="2021-01-15T03:18:30.209Z">
<meta property="article:modified_time" content="2021-03-26T11:55:02.307Z">
<meta property="article:author" content="小李博客">
<meta property="article:tag" content="JavaWeb">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png"><link rel="shortcut icon" href="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png"><link rel="canonical" href="http://xiaoliblog.cn/page/JavaWeb09"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/instantsearch.js@2.10.5/dist/instantsearch.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/instantsearch.js@2.10.5/dist/instantsearch.min.js" defer></script><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: {"appId":"RTG4CPNBLJ","apiKey":"f1745bdad68ceec57653b78244fe332c","indexName":"MyBlogIndex","hits":{"per_page":6},"languages":{"input_placeholder":"搜索文章","hits_empty":"找不到您查询的内容：${query}","hits_stats":"找到 ${hits} 条结果，用时 ${time} 毫秒"}},
  localSearch: undefined,
  translate: {"defaultEncoding":2,"translateDelay":0,"msgToTraditionalChinese":"繁","msgToSimplifiedChinese":"簡"},
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":200},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '天',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: {"limitCount":100,"languages":{"author":"作者: 小李博客","link":"链接: ","source":"来源: 小李博客","info":"著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。"}},
  lightbox: 'mediumZoom',
  Snackbar: {"chs_to_cht":"你已切换为繁体","cht_to_chs":"你已切换为简体","day_to_night":"你已切换为深色模式","night_to_day":"你已切换为浅色模式","bgLight":"#49b1f5","bgDark":"#121212","position":"top-center"},
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isanchor: true
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = { 
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2021-03-26 19:55:02'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const fontSizeVal = saveToLocal.get('global-font-size')
    if (fontSizeVal !== undefined) {
      document.documentElement.style.setProperty('--global-font-size', fontSizeVal + 'px')
    }
    })(window)</script><link rel="stylesheet" href="/css/MyStyle/MyStyle.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="/css/MyStyle/tagStyle.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/zykjofficial/zykjresource@master/css/font-awesome-animation.min.css" media="defer" onload="this.media='all'"/><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/sviptzk/StaticFile_HEXO@latest/butterfly/css/font-awesome-animation.min.css" media="defer" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/sviptzk/StaticFile_HEXO@latest/butterfly/css/plugins.min.css" media="defer" onload="this.media='all'"><meta name="generator" content="Hexo 5.2.0"><link rel="alternate" href="/atom.xml" title="小李博客" type="application/atom+xml">
</head><body><div id="web_bg"></div><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="author-avatar"><img class="avatar-img" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">210</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">38</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">56</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/box/"><i class="fa-fw fa fa-briefcase"></i><span> 工具箱</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-book"></i><span> 找文章</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-address-card"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="not-top-img" id="page-header"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">小李博客</a></span><div id="menus"><div id="search-button"><a class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 主页</span></a></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 友链</span></a></div><div class="menus_item"><a class="site-page" href="/box/"><i class="fa-fw fa fa-briefcase"></i><span> 工具箱</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-book"></i><span> 找文章</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></li><li><a class="site-page child" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></li><li><a class="site-page child" href="/categories/"><i class="fa-fw fa fa-folder-open"></i><span> 分类</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fa fa-address-card"></i><span> 关于</span></a></div><div class="menus_item"><a class="site-page" href="/messageboard/"><i class="fa-fw fa fa-paper-plane"></i><span> 留言</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav></header><main class="layout" id="content-inner"><div id="post"><div id="post-info"><h1 class="post-title">JavaWeb🌍数据库技术</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2021-01-15T03:18:30.209Z" title="发表于 2021-01-15 11:18:30">2021-01-15</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2021-03-26T11:55:02.307Z" title="更新于 2021-03-26 19:55:02">2021-03-26</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/Web%E5%89%8D%E7%AB%AF/">Web前端</a><i class="fas fa-angle-right post-meta-separator"></i><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/Web%E5%89%8D%E7%AB%AF/JavaWeb/">JavaWeb</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">5.4k</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>23分钟</span></span><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="JavaWeb🌍数据库技术"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">阅读量:</span><span id="busuanzi_value_page_pv"></span></span></div></div></div><article class="post-content" id="article-container"><h1 id="JDBC简介"><a href="#JDBC简介" class="headerlink" title="JDBC简介"></a>JDBC简介</h1><ul>
<li>JDBC全称是Java数据库连接（Java Database Connectivity），它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据的查询、更新、新增和删除的操作</li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/8438d24f18438c5609acb9a5a447927f.png" width="50%">

<ul>
<li>应用程序使用JDBC访问特定的数据库时，需要与不同的数据库驱动进行连接。由于不同数据库厂商提供的数据库驱动不同，因此，为了使应用程序与数据库真正建立连接，JDBC不仅需要提供访问数据库的API，还需要封装与各种数据库服务器通信的细节</li>
</ul>
<img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@4a9e4cb23e41b792cdf91f0650711f43feca2ee9/2021/03/26/ec0ac0b0eac170a94ae3d5641effb085.png" width="70%">

<ul>
<li>需要jar包的支持<ul>
<li>java.sql</li>
<li>javax.sql</li>
<li>mysql-conneter-java..连接驱动</li>
</ul>
</li>
</ul>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">&lt;!-- 连接MySQL数据--&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">   <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>mysql<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">   <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>mysql-connector-java<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">   <span class="tag">&lt;<span class="name">version</span>&gt;</span>5.1.46<span class="tag">&lt;/<span class="name">version</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br></pre></td></tr></table></figure>

<ul>
<li>在IDEA中连接数据库</li>
</ul>
<h1 id="常用SQL语句"><a href="#常用SQL语句" class="headerlink" title="常用SQL语句"></a>常用SQL语句</h1><h2 id="创建表和数据库"><a href="#创建表和数据库" class="headerlink" title="创建表和数据库"></a>创建表和数据库</h2><ul>
<li>创建数据库</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">Create</span> <span class="keyword">database</span>-<span class="keyword">name</span></span><br></pre></td></tr></table></figure>

<ul>
<li>创建表</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> student(sno <span class="built_in">int</span> primary <span class="keyword">key</span>);</span><br></pre></td></tr></table></figure>

<h2 id="CRUD"><a href="#CRUD" class="headerlink" title="CRUD"></a>CRUD</h2><ul>
<li>增加一个列</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> student <span class="keyword">add</span> sname <span class="built_in">varchar</span>(<span class="number">10</span>);</span><br></pre></td></tr></table></figure>

<ul>
<li>插入数据</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> student (sno,sname) <span class="keyword">values</span>(<span class="number">10</span>,<span class="string">&#x27;程子为&#x27;</span>);</span><br></pre></td></tr></table></figure>

<ul>
<li>查询数据</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student;</span><br></pre></td></tr></table></figure>

<ul>
<li>解决中文乱码</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">set</span> <span class="keyword">names</span>  gbk;</span><br></pre></td></tr></table></figure>

<ul>
<li>修改数据（如果出现中文不能插入的情况，需要设置编码）</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">update</span> student <span class="keyword">set</span> sno=<span class="number">1</span> <span class="keyword">where</span> sname=<span class="string">&#x27;程子为&#x27;</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>删除数据</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> student <span class="keyword">where</span> sno=<span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>在某一行的指定列添加数据（更新和插入的区别）</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span>  student <span class="keyword">add</span> sage <span class="built_in">int</span>;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span>  student <span class="keyword">add</span> ssex <span class="built_in">varchar</span>(<span class="number">5</span>);</span><br><span class="line"><span class="keyword">update</span> student <span class="keyword">set</span> sage=<span class="number">20</span>,ssex=<span class="string">&#x27;男&#x27;</span> <span class="keyword">where</span> sno=<span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>对列的操作</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">查看列：desc 表名;</span><br><span class="line">修改表名：<span class="keyword">alter</span> <span class="keyword">table</span> t_book <span class="keyword">rename</span> <span class="keyword">to</span> bbb;</span><br><span class="line">添加列：<span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> <span class="keyword">column</span> 列名 <span class="built_in">varchar</span>(<span class="number">30</span>);</span><br><span class="line">删除列：<span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">drop</span> <span class="keyword">column</span> 列名;</span><br><span class="line">改列名MySQL： <span class="keyword">alter</span> <span class="keyword">table</span> bbb <span class="keyword">change</span> nnnnn hh <span class="built_in">int</span>;</span><br></pre></td></tr></table></figure>

<h2 id="查询条件"><a href="#查询条件" class="headerlink" title="查询条件"></a>查询条件</h2><ul>
<li>排序</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">降序： <span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">order</span> <span class="keyword">by</span> sage <span class="keyword">desc</span>;</span><br><span class="line">升序： <span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">order</span> <span class="keyword">by</span> sage <span class="keyword">asc</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>求总数</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>（*） <span class="keyword">from</span> student;</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> student <span class="keyword">where</span> sage=<span class="number">20</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>求和</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">sum</span>(sage) <span class="keyword">as</span> age  <span class="keyword">from</span> student;</span><br></pre></td></tr></table></figure>

<ul>
<li>求平均</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">avg</span>(sage) <span class="keyword">as</span> 平均年龄  <span class="keyword">from</span> student;</span><br></pre></td></tr></table></figure>

<ul>
<li>最大值</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">max</span>(sage) <span class="keyword">as</span> 最大年龄  <span class="keyword">from</span> student;</span><br></pre></td></tr></table></figure>

<ul>
<li>最小值</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">min</span>(sage) <span class="keyword">as</span> 最大年龄  <span class="keyword">from</span> student;</span><br></pre></td></tr></table></figure>

<ul>
<li>分组</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> sage,<span class="keyword">count</span>(*) <span class="keyword">from</span> student <span class="keyword">group</span> <span class="keyword">by</span> sage;</span><br></pre></td></tr></table></figure>

<ul>
<li>多条件查询</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student <span class="keyword">where</span> sname=<span class="string">&#x27;小红&#x27;</span> <span class="keyword">and</span> sno=<span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>多表查询</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> student,course <span class="keyword">where</span> student.sno=course.sno <span class="keyword">and</span> student.sno=<span class="number">6</span>;</span><br></pre></td></tr></table></figure>

<h1 id="JDBC常用API"><a href="#JDBC常用API" class="headerlink" title="JDBC常用API"></a>JDBC常用API</h1><ul>
<li><p>Driver接口：所有JDBC驱动必须实现的接口。<br>该接口专门提供给数据库厂商使用。在编写JDBC程序时，必须要把指定数据库驱动程序或类库加载到项目的classpath中。</p>
</li>
<li><p>DriverManager类：加载JDBC驱动并创建与数据库的连接。<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/5cc8e5fa39a85e28a673a9cbf70b5cac.png"></p>
</li>
<li><p>Connection接口：代表Java程序和数据库的连接。<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/7cfd0553765725530ffef326d22d70b5.png"></p>
</li>
<li><p>Statement接口：执行静态的SQL语句，并返回一个结果对象。<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/959eb6d5cedfeb8379a8e3344b145b69.png"></p>
</li>
<li><p>PreparedStatement接口：执行预编译的SQL语句。<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/3465f4d014ffdb30f2342a4f4dba7d77.png"></p>
</li>
<li><p>ResultSet接口：保存JDBC执行查询时返回的结果集。<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/3792e3234a44f5647d84a16d6638ea3e.png"></p>
</li>
</ul>
<h2 id="实现JDBC程序"><a href="#实现JDBC程序" class="headerlink" title="实现JDBC程序"></a>实现JDBC程序</h2><ul>
<li>配置信息</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//配置信息</span></span><br><span class="line"><span class="comment">//useUnicode=true&amp;characterEncoding=UTF-8 解决中文乱码问题</span></span><br><span class="line">String url = <span class="string">&quot;jdbc:mysql://localhost:3306/DataBase_Name?useUnicode=true&amp;characterEncoding=UTF-8&quot;</span>;</span><br><span class="line">String username = <span class="string">&quot;root&quot;</span>;</span><br><span class="line">String password = <span class="string">&quot;root&quot;</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>加载并注册数据库驱动</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Class.forName(<span class="string">&quot;com.mysql.jdbc.Driver&quot;</span>);</span><br></pre></td></tr></table></figure>

<ul>
<li>通过<code>DriverManager</code>获取数据库连接</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Connection connection = DriverManager.getConnection(url, username, password);</span><br></pre></td></tr></table></figure>

<ul>
<li>通过<code>Connection</code>对象获取向数据库发送SQL的对象<code>statement</code></li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Statement statement = connection.createStatement();</span><br></pre></td></tr></table></figure>

<ul>
<li>编写SQL语句</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">String sql = <span class="string">&quot;select * from user&quot;</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>使用Statement执行SQL语句</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ResultSet rs = statement.executeQuery(sql);</span><br></pre></td></tr></table></figure>

<ul>
<li>操作<code>ResultSet</code>结果集</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">while</span>(rs.next())&#123;</span><br><span class="line">   <span class="comment">//不知道数据类型就使用Object</span></span><br><span class="line">   <span class="keyword">int</span> id = rs.getObject(<span class="string">&quot;id&quot;</span>); <span class="comment">// 通过列名获取指定字段的值</span></span><br><span class="line">   String name = rs.getObject(<span class="string">&quot;name&quot;</span>);</span><br><span class="line">   String psd =rs.getObject(<span class="string">&quot;pwd&quot;</span>);</span><br><span class="line">   System.out.println(<span class="string">&quot;| &quot;</span>+id + <span class="string">&quot; | &quot;</span> + name + <span class="string">&quot; | &quot;</span> + psd + <span class="string">&quot; | &quot;</span>);</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<ul>
<li>关闭连接,释放资源（先开后关）</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">rs.close();</span><br><span class="line">statement.close();</span><br></pre></td></tr></table></figure>

<ul>
<li>搭建数据库</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">DATABASE</span> <span class="string">`mybatis`</span>;</span><br><span class="line"><span class="keyword">USE</span> <span class="string">`mybatis`</span>;</span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`user`</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`user`</span> (</span><br><span class="line"><span class="string">`id`</span> <span class="built_in">INT</span>(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span> PRIMARY <span class="keyword">KEY</span>,</span><br><span class="line"><span class="string">`name`</span> <span class="built_in">VARCHAR</span>(<span class="number">30</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span>,</span><br><span class="line"><span class="string">`pwd`</span> <span class="built_in">VARCHAR</span>(<span class="number">30</span>) <span class="keyword">DEFAULT</span> <span class="literal">NULL</span></span><br><span class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">INNODB</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8;</span><br><span class="line"></span><br><span class="line"><span class="keyword">INSERT</span>  <span class="keyword">INTO</span> <span class="string">`user`</span>(<span class="string">`id`</span>,<span class="string">`name`</span>,<span class="string">`pwd`</span>) <span class="keyword">VALUES</span> (<span class="number">1</span>,<span class="string">&#x27;狂神&#x27;</span>,<span class="string">&#x27;123456&#x27;</span>),(<span class="number">2</span>,<span class="string">&#x27;张三&#x27;</span>,<span class="string">&#x27;abcdef&#x27;</span>),(<span class="number">3</span>,<span class="string">&#x27;李四&#x27;</span>,<span class="string">&#x27;987654&#x27;</span>);</span><br></pre></td></tr></table></figure>

<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.lzy.jdbc;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.*;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">TestJdbc</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> ClassNotFoundException, SQLException </span>&#123;</span><br><span class="line">        <span class="comment">//配置信息</span></span><br><span class="line">        <span class="comment">//useUnicode=true&amp;characterEncoding=UTF-8 解决中文乱码问题</span></span><br><span class="line">        String url = <span class="string">&quot;jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=UTF-8&quot;</span>;</span><br><span class="line">        String username = <span class="string">&quot;root&quot;</span>;</span><br><span class="line">        String password = <span class="string">&quot;root&quot;</span>;</span><br><span class="line"></span><br><span class="line">        <span class="comment">//1. 加载驱动</span></span><br><span class="line">        Class.forName(<span class="string">&quot;com.mysql.jdbc.Driver&quot;</span>);</span><br><span class="line">        <span class="comment">//2. 连接数据库，connection代表数据库</span></span><br><span class="line">        Connection connection = DriverManager.getConnection(url, username, password);</span><br><span class="line">        <span class="comment">//3.向数据库发送SQL的对象statement</span></span><br><span class="line">        Statement statement = connection.createStatement();</span><br><span class="line">        <span class="comment">//4. 编写SQL</span></span><br><span class="line">        String sql = <span class="string">&quot;select * from user&quot;</span>;</span><br><span class="line">        <span class="comment">//5. 执行查询SQL,返回一个ResultSet结果集</span></span><br><span class="line">        ResultSet rs = statement.executeQuery(sql);</span><br><span class="line">        <span class="comment">//6. 处理结果集</span></span><br><span class="line">        <span class="keyword">while</span>(rs.next())&#123;</span><br><span class="line">            Object id = rs.getObject(<span class="string">&quot;id&quot;</span>);</span><br><span class="line">            Object name = rs.getObject(<span class="string">&quot;name&quot;</span>);</span><br><span class="line">            Object psd =rs.getObject(<span class="string">&quot;pwd&quot;</span>);</span><br><span class="line">            System.out.println(<span class="string">&quot;| &quot;</span>+id + <span class="string">&quot; | &quot;</span> + name + <span class="string">&quot; | &quot;</span> + psd + <span class="string">&quot; | &quot;</span>);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//6. 关闭连接,释放资源（先开后关）</span></span><br><span class="line">        rs.close();</span><br><span class="line">        statement.close();</span><br><span class="line">        connection.close();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="PreparedStatement对象"><a href="#PreparedStatement对象" class="headerlink" title="PreparedStatement对象"></a>PreparedStatement对象</h2><ul>
<li><code>PreparedStatement</code>对象可以对SQL语句进行预编译，预编译的信息会存储在该对象中</li>
<li>当相同的SQL语句再次执行时，程序会使用PreparedStatement对象中的数据，而不需要对SQL语句再次编译去查询数据库，这样就大大的提高了数据的访问效率</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.lzy.jdbc;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.*;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">TestJdbc</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> ClassNotFoundException, SQLException </span>&#123;</span><br><span class="line">        <span class="comment">//配置信息</span></span><br><span class="line">        <span class="comment">//useUnicode=true&amp;characterEncoding=UTF-8 解决中文乱码问题</span></span><br><span class="line">        String url = <span class="string">&quot;jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=UTF-8&quot;</span>;</span><br><span class="line">        String username = <span class="string">&quot;root&quot;</span>;</span><br><span class="line">        String password = <span class="string">&quot;root&quot;</span>;</span><br><span class="line"></span><br><span class="line">        <span class="comment">//1. 加载驱动</span></span><br><span class="line">        Class.forName(<span class="string">&quot;com.mysql.jdbc.Driver&quot;</span>);</span><br><span class="line">        <span class="comment">//2. 连接数据库，connection代表数据库</span></span><br><span class="line">        Connection connection = DriverManager.getConnection(url, username, password);</span><br><span class="line">        <span class="comment">//3. 编写SQL</span></span><br><span class="line">        String sql = <span class="string">&quot;insert into user(id,name,pwd) values (?,?,?)&quot;</span>;</span><br><span class="line">        <span class="comment">//4.预编译</span></span><br><span class="line">        PreparedStatement preparedStatement = connection.prepareStatement(sql);</span><br><span class="line">        preparedStatement.setInt(<span class="number">1</span>,<span class="number">1</span>); <span class="comment">//给第一个问号占位符赋值</span></span><br><span class="line">        preparedStatement.setString(<span class="number">2</span>,<span class="string">&quot;李白&quot;</span>); <span class="comment">//给第二个问号占位符赋值</span></span><br><span class="line">        preparedStatement.setString(<span class="number">3</span>,<span class="string">&quot;123ff&quot;</span>); <span class="comment">//给第三个问号占位符赋值</span></span><br><span class="line"></span><br><span class="line">        <span class="comment">//5. 执行SQL</span></span><br><span class="line">        <span class="keyword">int</span> i = preparedStatement.executeUpdate();</span><br><span class="line">        <span class="keyword">if</span>(i&gt;<span class="number">0</span>)&#123;</span><br><span class="line">            System.out.println(<span class="string">&quot;插入成功&quot;</span>);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//6. 关闭连接,释放资源（先开后关）</span></span><br><span class="line">        preparedStatement.close();</span><br><span class="line">        connection.close();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h2 id="ResultSet对象"><a href="#ResultSet对象" class="headerlink" title="ResultSet对象"></a>ResultSet对象</h2><ul>
<li><code>ResultSet</code>主要用于存储结果集，可以通过<code>next()</code>方法由前向后逐个获取结果集中的数据，如果想获取结果集中任意位置的数据，则需要在创建Statement对象时，设置两个ResultSet定义的常量，具体设置方式如下：</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">Statement st = conn.createStatement(</span><br><span class="line">   ResultSet.TYPE_SCROLL_INSENITIVE, </span><br><span class="line">   ResultSet.CONCUR_READ_ONLY</span><br><span class="line">);</span><br><span class="line">ResultSet rs = st.excuteQuery(sql);</span><br></pre></td></tr></table></figure>

<ul>
<li>常量<code>Result.TYPE_SCROLL_INSENITIVE</code>表示结果集可滚动</li>
<li>常量<code>ResultSet.CONCUR_READ_ONLY</code>表示以只读形式打开结果集</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.lzy.jdbc;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.*;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">TestJdbc</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> ClassNotFoundException, SQLException </span>&#123;</span><br><span class="line">        <span class="comment">//配置信息</span></span><br><span class="line">        <span class="comment">//useUnicode=true&amp;characterEncoding=UTF-8 解决中文乱码问题</span></span><br><span class="line">        String url = <span class="string">&quot;jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=UTF-8&quot;</span>;</span><br><span class="line">        String username = <span class="string">&quot;root&quot;</span>;</span><br><span class="line">        String password = <span class="string">&quot;root&quot;</span>;</span><br><span class="line"></span><br><span class="line">        <span class="comment">//1. 加载驱动</span></span><br><span class="line">        Class.forName(<span class="string">&quot;com.mysql.jdbc.Driver&quot;</span>);</span><br><span class="line">        <span class="comment">//2. 连接数据库，connection代表数据库</span></span><br><span class="line">        Connection connection = DriverManager.getConnection(url, username, password);</span><br><span class="line">        <span class="comment">//3. 编写SQL</span></span><br><span class="line">        String sql = <span class="string">&quot;select * from user&quot;</span>;</span><br><span class="line">        <span class="comment">//4. 创建Statement对象并设置常量</span></span><br><span class="line">        Statement st =connection.createStatement(</span><br><span class="line">                ResultSet.TYPE_SCROLL_INSENSITIVE,</span><br><span class="line">                ResultSet.CONCUR_READ_ONLY);</span><br><span class="line">        <span class="comment">//5. 执行SQL并将获取的数据信息存放在ResultSet中</span></span><br><span class="line">        ResultSet rs = st.executeQuery(sql);</span><br><span class="line">        <span class="comment">//6. 取出ResultSet中指定数据的信息</span></span><br><span class="line">        System.out.print(<span class="string">&quot;第2条数据的name值为:&quot;</span>);</span><br><span class="line">        rs.absolute(<span class="number">2</span>);        <span class="comment">//将指针定位到结果集中第2行数据</span></span><br><span class="line">        System.out.println(rs.getString(<span class="string">&quot;name&quot;</span>));</span><br><span class="line">        System.out.print(<span class="string">&quot;第1条数据的name值为:&quot;</span>);</span><br><span class="line">        rs.beforeFirst();      <span class="comment">//将指针定位到结果集中第1行数据之前</span></span><br><span class="line">        rs.next();              <span class="comment">//将指针向后滚动</span></span><br><span class="line">        System.out.println(rs.getString(<span class="string">&quot;name&quot;</span>));</span><br><span class="line">        System.out.print(<span class="string">&quot;第4条数据的name值为:&quot;</span>);</span><br><span class="line">        rs.afterLast();        <span class="comment">//将指针定位到结果集中最后一条数据之后</span></span><br><span class="line">        rs.previous();         <span class="comment">//将指针向前滚动</span></span><br><span class="line">        System.out.println(rs.getString(<span class="string">&quot;name&quot;</span>));</span><br><span class="line">        <span class="comment">//6. 关闭连接,释放资源（先开后关）</span></span><br><span class="line">        rs.close();</span><br><span class="line">        st.close();</span><br><span class="line">        connection.close();</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<p><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@65418ab8f01c3b2967a1d3287ddd1f70979c6458/2021/03/26/4475d8228faceed063a906d58bdc4bd1.png"></p>
<h1 id="JDBC事务"><a href="#JDBC事务" class="headerlink" title="JDBC事务"></a>JDBC事务</h1><ul>
<li>数据库事务（Transaction）是由若干个SQL语句构成的一个操作序列，有点类似于Java的<code>synchronized</code>同步。数据库系统保证在一个事务中的所有SQL要么全部执行成功，要么全部不执行，即数据库事务具有<code>ACID</code>特性<ul>
<li>Atomicity：原子性</li>
<li>Consistency：一致性</li>
<li>Isolation：隔离性</li>
<li>Durability：持久性</li>
</ul>
</li>
</ul>
<h2 id="事务概述"><a href="#事务概述" class="headerlink" title="事务概述"></a>事务概述</h2><ul>
<li>对应用程序来说，数据库事务非常重要，很多运行着关键任务的应用程序，都必须依赖数据库事务保证程序的结果正常。</li>
<li>举个例子：假设<code>A</code>准备给<code>B</code>支付100，那么用两条SQL语句操作如下：</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">update</span> <span class="keyword">account</span> <span class="keyword">set</span> money = money<span class="number">-100</span> <span class="keyword">where</span> <span class="keyword">name</span> = <span class="string">&#x27;A&#x27;</span> ;</span><br><span class="line"><span class="keyword">update</span> <span class="keyword">account</span> <span class="keyword">set</span> money = money+<span class="number">100</span> <span class="keyword">where</span> <span class="keyword">name</span> = <span class="string">&#x27;B&#x27;</span> ;</span><br></pre></td></tr></table></figure>

<ul>
<li>这两条语句必须以<strong>事务方式</strong>执行才能保证业务的正确性，因为一旦第一条SQL执行成功而第二条SQL失败的话，系统的钱就会凭空减少100，而有了事务，要么这笔转账成功，要么转账失败，双方账户的钱都不变</li>
<li>要在JDBC中执行事务，本质上就是如何把多条SQL包裹在一个数据库事务中执行。我们来看JDBC的事务代码</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">Connection conn = openConnection();</span><br><span class="line"><span class="keyword">try</span> &#123;</span><br><span class="line">    <span class="comment">// 关闭自动提交:</span></span><br><span class="line">    conn.setAutoCommit(<span class="keyword">false</span>);</span><br><span class="line">    <span class="comment">// 执行多条SQL语句:</span></span><br><span class="line">    insert(); update(); delete();</span><br><span class="line">    <span class="comment">// 提交事务:</span></span><br><span class="line">    conn.commit();</span><br><span class="line">&#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">    <span class="comment">// 回滚事务:</span></span><br><span class="line">    conn.rollback();</span><br><span class="line">&#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">    conn.setAutoCommit(<span class="keyword">true</span>);</span><br><span class="line">    conn.close();</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<ul>
<li>其中，开启事务的关键代码是<code>conn.setAutoCommit(false)</code>，表示关闭自动提交</li>
<li>提交事务的代码在执行完指定的若干条SQL语句后，调用<code>conn.commit()</code></li>
<li>要注意事务不是总能成功，如果事务提交失败，会抛出SQL异常（也可能在执行SQL语句的时候就抛出了），此时我们必须捕获并调用<code>conn.rollback()</code>回滚事务</li>
<li>最后，在<code>finally</code>中通过<code>conn.setAutoCommit(true)</code>把<code>Connection</code>对象的状态恢复到初始值</li>
</ul>
<h2 id="测试事务"><a href="#测试事务" class="headerlink" title="测试事务"></a>测试事务</h2><ul>
<li>Junit单元测试工具可以不需要<code>main</code>方法就可以执行，只需要一个注解就可以随时测试</li>
<li>首先引入Junit测试工具依赖</li>
</ul>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="tag">&lt;<span class="name">dependency</span>&gt;</span></span><br><span class="line">   <span class="tag">&lt;<span class="name">groupId</span>&gt;</span>junit<span class="tag">&lt;/<span class="name">groupId</span>&gt;</span></span><br><span class="line">   <span class="tag">&lt;<span class="name">artifactId</span>&gt;</span>junit<span class="tag">&lt;/<span class="name">artifactId</span>&gt;</span></span><br><span class="line">   <span class="tag">&lt;<span class="name">version</span>&gt;</span>4.11<span class="tag">&lt;/<span class="name">version</span>&gt;</span></span><br><span class="line">   <span class="tag">&lt;<span class="name">scope</span>&gt;</span>test<span class="tag">&lt;/<span class="name">scope</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">dependency</span>&gt;</span></span><br></pre></td></tr></table></figure>

<ul>
<li>使用：在方法上使用注解<code>@Test</code>，且只在方法上使用</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@Test</span></span><br><span class="line"><span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">Test</span><span class="params">()</span></span>&#123;</span><br><span class="line">   System.out.println(<span class="string">&quot;不需要main方法也可以执行！&quot;</span>);</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<ul>
<li>创建数据库</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">USE</span> mybatis</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="keyword">account</span>(</span><br><span class="line">   <span class="keyword">id</span> <span class="built_in">INT</span> PRIMARY <span class="keyword">KEY</span> AUTO_INCREMENT,</span><br><span class="line">   <span class="keyword">NAME</span> <span class="built_in">VARCHAR</span>(<span class="number">40</span>),</span><br><span class="line">   money <span class="built_in">FLOAT</span></span><br><span class="line">);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="keyword">account</span>(<span class="keyword">NAME</span>,money) <span class="keyword">VALUES</span>(<span class="string">&#x27;A&#x27;</span>,<span class="number">1000</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="keyword">account</span>(<span class="keyword">NAME</span>,money) <span class="keyword">VALUES</span>(<span class="string">&#x27;B&#x27;</span>,<span class="number">1000</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="keyword">account</span>(<span class="keyword">NAME</span>,money) <span class="keyword">VALUES</span>(<span class="string">&#x27;C&#x27;</span>,<span class="number">1000</span>);</span><br></pre></td></tr></table></figure>

<ul>
<li>Java完成数据库事务操作</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> com.lzy.jdbc;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.Connection;</span><br><span class="line"><span class="keyword">import</span> java.sql.DriverManager;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">JDBC_Commit</span> </span>&#123;</span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> ClassNotFoundException, SQLException </span>&#123;</span><br><span class="line">        <span class="comment">//配置信息</span></span><br><span class="line">        <span class="comment">//useUnicode=true&amp;characterEncoding=UTF-8 解决中文乱码问题</span></span><br><span class="line">        String url = <span class="string">&quot;jdbc:mysql://localhost:3306/mybatis?useUnicode=true&amp;characterEncoding=UTF-8&quot;</span>;</span><br><span class="line">        String username = <span class="string">&quot;root&quot;</span>;</span><br><span class="line">        String password = <span class="string">&quot;root&quot;</span>;</span><br><span class="line"></span><br><span class="line">        Connection connection = <span class="keyword">null</span>;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            <span class="comment">//1. 加载驱动</span></span><br><span class="line">            Class.forName(<span class="string">&quot;com.mysql.jdbc.Driver&quot;</span>);</span><br><span class="line">            <span class="comment">//2. 连接数据库，connection代表数据库</span></span><br><span class="line">            connection = DriverManager.getConnection(url, username, password);</span><br><span class="line">            <span class="comment">//3. 通知数据库开启事务,false开启</span></span><br><span class="line">            connection.setAutoCommit(<span class="keyword">false</span>);</span><br><span class="line">            String sql1 = <span class="string">&quot;update account set money = money-100 where name = &#x27;A&#x27;&quot;</span>;</span><br><span class="line">            connection.prepareStatement(sql1).executeUpdate();</span><br><span class="line"></span><br><span class="line">            <span class="comment">//制造错误</span></span><br><span class="line">            <span class="comment">//int i = 1 / 0;</span></span><br><span class="line"></span><br><span class="line">            String sql2 = <span class="string">&quot;update account set money = money+100 where name = &#x27;B&#x27;&quot;</span>;</span><br><span class="line">            connection.prepareStatement(sql2).executeUpdate();</span><br><span class="line"></span><br><span class="line">            <span class="comment">//提交事务</span></span><br><span class="line">            connection.commit(); <span class="comment">//以上两条SQL都提交成功了就提交事务</span></span><br><span class="line">            System.out.println(<span class="string">&quot;success&quot;</span>);</span><br><span class="line">        &#125;<span class="keyword">catch</span> (Exception e)&#123;</span><br><span class="line">            <span class="keyword">try</span>&#123;</span><br><span class="line">                <span class="comment">//如果出现移除，就通知数据库回滚事务</span></span><br><span class="line">                connection.rollback();</span><br><span class="line">            &#125;<span class="keyword">catch</span>(SQLException e1)&#123;</span><br><span class="line">                e1.printStackTrace();</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>


<ul>
<li>出现错误时，数据库数据如下</li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@eb45e99b37a3bd50a6386596b0649202a60911c8/2021/03/26/6bd1603d7e80dfb21b5cfb77ae67dc23.png"></p>
<ul>
<li>未出现错误，数据库如下，可以发现转账成功未出现错误</li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@eec49bb39d564309ad48dcc6772215cf4629833f/2021/03/26/c22a5c7eba30bd43017e19df6752e04a.png"></p>
<h1 id="数据库连接池"><a href="#数据库连接池" class="headerlink" title="数据库连接池"></a>数据库连接池</h1><ul>
<li><p>在JDBC编程中，每次创建和断开Connection对象都会消耗一定的时间和IO资源。频繁地创建、断开数据库连接势必会影响数据库的访问效率，甚至导致数据库崩溃。为了避免频繁的创建数据库连接，工程师们提出了数据库连接池技术</p>
</li>
<li><p>数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用现有的数据库连接，而不是重新建立。下面通过一张图来简单描述应用程序如何通过连接池连接数据库</p>
</li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/862e2d18125cef97df103b696c3fbaf9.png"></p>
<h2 id="DataSource接口"><a href="#DataSource接口" class="headerlink" title="DataSource接口"></a>DataSource接口</h2><ul>
<li>为了获取数据库连接对象（Connection），JDBC提供了javax.sql.DataSource接口，它负责与数据库建立连接，并定义了返回值为Connection对象的方法：</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="function">Connection <span class="title">getConnection</span><span class="params">()</span> </span></span><br><span class="line"><span class="function">Connection <span class="title">getConnection</span><span class="params">(String username, String password)</span></span></span><br></pre></td></tr></table></figure>

<ul>
<li>我们习惯性的把实现了javax.sql.DataSource接口的类称为数据源，顾名思义，数据源即数据的来源。在数据源中存储了所有建立数据库连接的信息。</li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/73a8871e6520e1c0a06baf06aff4c683.png"></p>
<h2 id="DBCP数据源"><a href="#DBCP数据源" class="headerlink" title="DBCP数据源"></a>DBCP数据源</h2><ul>
<li>DBCP是数据库连接池（DataBase Connection Pool）的简称，是Apache组织下的开源连接池实现，也是Tomcat服务器使用的连接池组件。单独使用DBCP数据源时，需要在应用程序中导入两个jar包。这两个包可以在<a target="_blank" rel="noopener" href="http://commons.apache.org/proper/">Apache官网</a>下载</li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/2bcc67086852073b7d41513ea2e5d6eb.png"></p>
<ul>
<li><p>Commons-dbcp.jar：连接池的实现</p>
</li>
<li><p>Commons-pool.jar：连接池实现的依赖库</p>
</li>
<li><p>commons-dbcp.jar包中包含两个核心类，分别是BasicDataSourceFactory和BasicDataSource，它们都包含获取DBCP数据源对象的方法。</p>
</li>
<li><p>BasicDataSource是DataSource接口的实现类，主要包括设置数据源对象的方法。</p>
</li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/9a6a498088470a37a812d95dfa3304c3.png"></p>
<ul>
<li>数据源对象创建的两种方式<ol>
<li>使用BasicDataSource类创建一个数据源对象，手动给数据源对象设置属性值，然后获取数据库连接对象。</li>
<li>使用BasicDataSourceFactory工厂类读取配置文件，创建数据源对象，然后获取数据库连接对象。</li>
</ol>
</li>
</ul>
<h2 id="C3P0数据源"><a href="#C3P0数据源" class="headerlink" title="C3P0数据源"></a>C3P0数据源</h2><ul>
<li>C3P0是目前最流行的开源数据库连接池之一，它实现了DataSource数据源接口，支持JDBC2和JDBC3的标准规范，易于扩展并且性能优越，著名的开源框架Hibernate和 Spring使用的都是该数据源。编程之前需要导入<code>c3p0-0.9.1.2jar</code>包</li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/1abed06828422708d5d78d166a3c2653.png"></p>
<ul>
<li>我们在使用C3P0数据源开发时，需要了解C3P0中DataSource接口的实现类ComboPooledDataSource，它是C3P0的核心类，提供了数据源对象的相关方法。</li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/f5749b855745e48b0158de36f37db5be.png"></p>
<ul>
<li>数据源对象创建的两种方式<ol>
<li>使用ComboPooledDataSource()构造方法创建数据源对象，需要手动给数据源对象设置属性值，然后获取数据库连接对象。<br>2 使用ComboPooledDataSource(String configName)构造方法读取c3p0-config.xml配置文件，从而创建数据源对象，然后获取数据库连接对象。（推荐使用）</li>
</ol>
</li>
</ul>
<p><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/90831d536edff41388ad8143ff4b819f.png"></p>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">c3p0-config</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">default-config</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;driverClass&quot;</span>&gt;</span>com.mysql.jdbc.Driver<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;jdbcUrl&quot;</span>&gt;</span></span><br><span class="line">     		jdbc:mysql://localhost:3306/student</span><br><span class="line">     	<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;user&quot;</span>&gt;</span>root<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;password&quot;</span>&gt;</span>root<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;checkoutTimeout&quot;</span>&gt;</span>30000<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;initialPoolSize&quot;</span>&gt;</span>10<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;maxIdleTime&quot;</span>&gt;</span>30<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;maxPoolSize&quot;</span>&gt;</span>100<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;minPoolSize&quot;</span>&gt;</span>10<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;maxStatements&quot;</span>&gt;</span>200<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">default-config</span>&gt;</span> </span><br><span class="line">	<span class="tag">&lt;<span class="name">named-config</span> <span class="attr">name</span>=<span class="string">&quot;itcast&quot;</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;driverClass&quot;</span>&gt;</span>com.mysql.jdbc.Driver<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;jdbcUrl&quot;</span>&gt;</span></span><br><span class="line">           	jdbc:mysql://localhost:3306/student</span><br><span class="line">        <span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;user&quot;</span>&gt;</span>root<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;password&quot;</span>&gt;</span>root<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;initialPoolSize&quot;</span>&gt;</span>5<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;maxPoolSize&quot;</span>&gt;</span>15<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">named-config</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">c3p0-config</span>&gt;</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> c3p0;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">import</span> javax.sql.DataSource;</span><br><span class="line"><span class="keyword">import</span> com.mchange.v2.c3p0.ComboPooledDataSource;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">c3p0_test</span> </span>&#123;</span><br><span class="line">	<span class="keyword">public</span> <span class="keyword">static</span> DataSource ds = <span class="keyword">null</span>;</span><br><span class="line">	<span class="comment">// 初始化C3P0数据源</span></span><br><span class="line">	<span class="keyword">static</span> &#123;</span><br><span class="line">		<span class="comment">// 使用c3p0-config.xml配置文件中的named-config节点中name属性的值</span></span><br><span class="line">		ComboPooledDataSource cpds = <span class="keyword">new</span> ComboPooledDataSource(<span class="string">&quot;root&quot;</span>);</span><br><span class="line">		ds = cpds;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		System.out.println(ds.getConnection());</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<p><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/81c22010088b51234b34fdf60a6e12c5.png"></p>
<blockquote>
<p>注意，配置文件名必须为c3p0-config.xml或者c3p0.properties，并且位于该项目的src根目录下<br>当传入的configName的值为空或者不存在时，则使用默认的配置方式创建数据源</p>
</blockquote>
<h1 id="DBUtils工具"><a href="#DBUtils工具" class="headerlink" title="DBUtils工具"></a>DBUtils工具</h1><ul>
<li>为了更加简单地使用JDBC，Apache组织提供了一个工具类库commons-dbutils组件。<br>该组件实现了对JDBC的简单封装，可以在不影响性能的情况下极大简化JDBC的编码工作量。编码前需要导入<code>commons-dbutils-1.6.jar</code></li>
</ul>
<h2 id="QueryRunner类"><a href="#QueryRunner类" class="headerlink" title="QueryRunner类"></a>QueryRunner类</h2><ul>
<li>QueryRunner类简化了执行SQL语句的代码，它与ResultSetHandler组合在一起就能完成大部分的数据库操作，大大的减少了编码量。</li>
<li>QueryRunner类提供了带有一个参数的构造方法，该方法以javax.sql.DataSource作为参数传递到QueryRunner的构造方法中来获取Connection对象。针对不同的数据库操作，QueryRunner类提供了几种常见的方法，具体如下：</li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">query(String sql, ResultSetHandler rsh, Object… params)方法</span><br><span class="line">update(String sql, Object… params)方法</span><br><span class="line">update(String sql)方法</span><br></pre></td></tr></table></figure>

<div class="note success simple"><p><code>params</code>用SQL语句中的参数(?)置换，当有多个参数时有如下两种方法</p>
</div>

<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">update(sql,<span class="number">1</span>,<span class="number">2</span>,<span class="number">3</span>);  <span class="comment">//直接用逗号隔开</span></span><br><span class="line">update(sql,<span class="keyword">new</span> Object[]&#123;<span class="number">1</span>,<span class="number">2</span>,<span class="number">3</span>&#125;);  <span class="comment">//创建一个数组</span></span><br></pre></td></tr></table></figure>
<h2 id="ResultSetHandler接口"><a href="#ResultSetHandler接口" class="headerlink" title="ResultSetHandler接口"></a>ResultSetHandler接口</h2><ul>
<li>ResultSetHandler接口用于处理ResultSet结果集，它可以将结果集中的数据转为不同的形式。根据结果集中数据类型的不同，ResultSetHandler提供了几种常见的实现类，具体如下：<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/35af9fad43f9e640a8b35da64df05e28.png"></li>
</ul>
<h2 id="ResultSetHandler实现类"><a href="#ResultSetHandler实现类" class="headerlink" title="ResultSetHandler实现类"></a>ResultSetHandler实现类</h2><p><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/60428a3d66600833c459e7aa6d744a7f.png"></p>
<h2 id="DBUtils实现CURD"><a href="#DBUtils实现CURD" class="headerlink" title="DBUtils实现CURD"></a>DBUtils实现CURD</h2><p>项目工程总览<br><img src="https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/12/26/6da3beff6a7b6449492f773029ddd436.png"><br>搭建数据库</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">database</span> student;</span><br><span class="line"><span class="keyword">use</span> student;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="keyword">user</span>(<span class="keyword">id</span> <span class="built_in">int</span> primary <span class="keyword">key</span> auto_increment,<span class="keyword">name</span> <span class="built_in">varchar</span>(<span class="number">255</span>),<span class="keyword">password</span> <span class="built_in">varchar</span>(<span class="number">255</span>));</span><br></pre></td></tr></table></figure>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//封装用户信息</span></span><br><span class="line"><span class="keyword">package</span> UserBean;</span><br><span class="line"></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">User</span> </span>&#123;</span><br><span class="line">	<span class="keyword">private</span> <span class="keyword">int</span> id;</span><br><span class="line">	<span class="keyword">private</span> String name;</span><br><span class="line">	<span class="keyword">private</span> String password;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">int</span> <span class="title">getId</span><span class="params">()</span> </span>&#123;</span><br><span class="line">		<span class="keyword">return</span> id;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">setId</span><span class="params">(<span class="keyword">int</span> id)</span> </span>&#123;</span><br><span class="line">		<span class="keyword">this</span>.id = id;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> String <span class="title">getName</span><span class="params">()</span> </span>&#123;</span><br><span class="line">		<span class="keyword">return</span> name;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">setName</span><span class="params">(String name)</span> </span>&#123;</span><br><span class="line">		<span class="keyword">this</span>.name = name;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> String <span class="title">getPassword</span><span class="params">()</span> </span>&#123;</span><br><span class="line">		<span class="keyword">return</span> password;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">void</span> <span class="title">setPassword</span><span class="params">(String password)</span> </span>&#123;</span><br><span class="line">		<span class="keyword">this</span>.password = password;</span><br><span class="line">	&#125;</span><br><span class="line">	</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//创建C3p0类型</span></span><br><span class="line"><span class="keyword">package</span> C3p0_Utils;</span><br><span class="line"><span class="keyword">import</span> javax.sql.DataSource;</span><br><span class="line"><span class="keyword">import</span> com.mchange.v2.c3p0.ComboPooledDataSource;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">C3p0Utils</span> </span>&#123;</span><br><span class="line">	<span class="keyword">private</span> <span class="keyword">static</span> DataSource ds;</span><br><span class="line">	<span class="keyword">static</span> &#123;</span><br><span class="line">		ds = <span class="keyword">new</span> ComboPooledDataSource();</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> DataSource <span class="title">getDataSource</span><span class="params">()</span> </span>&#123;</span><br><span class="line">		<span class="keyword">return</span> ds;</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//封装了对User表增删查改的方法</span></span><br><span class="line"><span class="keyword">package</span> DBUtils_Dao;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">import</span> java.util.List;</span><br><span class="line"><span class="keyword">import</span> org.apache.commons.dbutils.QueryRunner;</span><br><span class="line"><span class="keyword">import</span> org.apache.commons.dbutils.handlers.BeanHandler;</span><br><span class="line"><span class="keyword">import</span> org.apache.commons.dbutils.handlers.BeanListHandler;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> C3p0_Utils.C3p0Utils;</span><br><span class="line"><span class="keyword">import</span> UserBean.User;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">DBUtilsDao</span> </span>&#123;</span><br><span class="line">	<span class="comment">// 查询所有，返回List集合</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> List <span class="title">findAll</span><span class="params">()</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		<span class="comment">// 创建QueryRunner对象</span></span><br><span class="line">		QueryRunner runner = <span class="keyword">new</span> QueryRunner(C3p0Utils.getDataSource());</span><br><span class="line">		<span class="comment">// 写SQL语句</span></span><br><span class="line">		String sql = <span class="string">&quot;select * from user&quot;</span>;</span><br><span class="line">		<span class="comment">// 调用方法</span></span><br><span class="line">		List list = (List) runner.query(sql,</span><br><span class="line">                     <span class="keyword">new</span> BeanListHandler(User.class));</span><br><span class="line">		<span class="keyword">return</span> list;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="comment">// 查询单个，返回对象</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> User <span class="title">find</span><span class="params">(<span class="keyword">int</span> id)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		<span class="comment">// 创建QueryRunner对象</span></span><br><span class="line">		QueryRunner runner = <span class="keyword">new</span> QueryRunner(C3p0Utils.getDataSource());</span><br><span class="line">		<span class="comment">// 写SQL语句</span></span><br><span class="line">		String sql = <span class="string">&quot;select * from user where id=?&quot;</span>;</span><br><span class="line">		<span class="comment">// 调用方法</span></span><br><span class="line">		User user = (User) runner.query(sql, </span><br><span class="line">                 <span class="keyword">new</span> BeanHandler(User.class), <span class="keyword">new</span> Object[] &#123; id &#125;);</span><br><span class="line">		<span class="keyword">return</span> user;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="comment">// 添加用户的操作</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> Boolean <span class="title">insert</span><span class="params">(User user)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		<span class="comment">// 创建QueryRunner对象</span></span><br><span class="line">		QueryRunner runner = <span class="keyword">new</span> QueryRunner(C3p0Utils.getDataSource());</span><br><span class="line">		<span class="comment">// 写SQL语句</span></span><br><span class="line">		String sql = <span class="string">&quot;insert into user (name,password) values (?,?)&quot;</span>;</span><br><span class="line">		<span class="comment">// 调用方法</span></span><br><span class="line">		<span class="keyword">int</span> num = runner.update(sql,</span><br><span class="line">				<span class="keyword">new</span> Object[] &#123; user.getName(), user.getPassword() &#125;);</span><br><span class="line">		<span class="keyword">if</span> (num &gt; <span class="number">0</span>)</span><br><span class="line">			<span class="keyword">return</span> <span class="keyword">true</span>;</span><br><span class="line">		<span class="keyword">return</span> <span class="keyword">false</span>;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="comment">// 修改用户的操作</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> Boolean <span class="title">update</span><span class="params">(User user)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		<span class="comment">// 创建QueryRunner对象</span></span><br><span class="line">		QueryRunner runner = <span class="keyword">new</span> QueryRunner(C3p0Utils.getDataSource());</span><br><span class="line">		<span class="comment">// 写SQL语句</span></span><br><span class="line">		String sql = <span class="string">&quot;update  user set name=?,password=? where id=?&quot;</span>;</span><br><span class="line">		<span class="comment">// 调用方法</span></span><br><span class="line">		<span class="keyword">int</span> num = runner.update(sql, <span class="keyword">new</span> Object[] &#123; user.getName(),</span><br><span class="line">                     user.getPassword(),user.getId() &#125;);</span><br><span class="line">		<span class="keyword">if</span> (num &gt; <span class="number">0</span>)</span><br><span class="line">			<span class="keyword">return</span> <span class="keyword">true</span>;</span><br><span class="line">		<span class="keyword">return</span> <span class="keyword">false</span>;</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="comment">// 删除用户的操作</span></span><br><span class="line">	<span class="function"><span class="keyword">public</span> Boolean <span class="title">delete</span><span class="params">(<span class="keyword">int</span> id)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		<span class="comment">// 创建QueryRunner对象</span></span><br><span class="line">		QueryRunner runner = <span class="keyword">new</span> QueryRunner(C3p0Utils.getDataSource());</span><br><span class="line">		<span class="comment">// 写SQL语句</span></span><br><span class="line">		String sql = <span class="string">&quot;delete from user where id=?&quot;</span>;</span><br><span class="line">		<span class="comment">// 调用方法</span></span><br><span class="line">		<span class="keyword">int</span> num = runner.update(sql, id);</span><br><span class="line">		<span class="keyword">if</span> (num &gt; <span class="number">0</span>)</span><br><span class="line">			<span class="keyword">return</span> <span class="keyword">true</span>;</span><br><span class="line">		<span class="keyword">return</span> <span class="keyword">false</span>;</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<figure class="highlight xml"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">&lt;!--c3p0-config配置--&gt;</span></span><br><span class="line"><span class="meta">&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;</span></span><br><span class="line"><span class="tag">&lt;<span class="name">c3p0-config</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;<span class="name">default-config</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;driverClass&quot;</span>&gt;</span>com.mysql.jdbc.Driver<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;jdbcUrl&quot;</span>&gt;</span></span><br><span class="line">     		jdbc:mysql://localhost:3306/student</span><br><span class="line">     	<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;user&quot;</span>&gt;</span>root<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;password&quot;</span>&gt;</span>root<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;checkoutTimeout&quot;</span>&gt;</span>30000<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;initialPoolSize&quot;</span>&gt;</span>10<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;maxIdleTime&quot;</span>&gt;</span>30<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;maxPoolSize&quot;</span>&gt;</span>100<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;minPoolSize&quot;</span>&gt;</span>10<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;maxStatements&quot;</span>&gt;</span>200<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">default-config</span>&gt;</span> </span><br><span class="line">	<span class="tag">&lt;<span class="name">named-config</span> <span class="attr">name</span>=<span class="string">&quot;itcast&quot;</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;driverClass&quot;</span>&gt;</span>com.mysql.jdbc.Driver<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;jdbcUrl&quot;</span>&gt;</span></span><br><span class="line">           	jdbc:mysql://localhost:3306/student</span><br><span class="line">        <span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;user&quot;</span>&gt;</span>root<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;password&quot;</span>&gt;</span>root<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;initialPoolSize&quot;</span>&gt;</span>5<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">		<span class="tag">&lt;<span class="name">property</span> <span class="attr">name</span>=<span class="string">&quot;maxPoolSize&quot;</span>&gt;</span>15<span class="tag">&lt;/<span class="name">property</span>&gt;</span></span><br><span class="line">	<span class="tag">&lt;/<span class="name">named-config</span>&gt;</span></span><br><span class="line"><span class="tag">&lt;/<span class="name">c3p0-config</span>&gt;</span></span><br><span class="line"></span><br></pre></td></tr></table></figure>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//插入数据</span></span><br><span class="line"><span class="keyword">package</span> DBUtils_Test;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">import</span> DBUtils_Dao.DBUtilsDao;</span><br><span class="line"><span class="keyword">import</span> UserBean.User;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">Insert_test</span> </span>&#123;</span><br><span class="line">	<span class="keyword">private</span> <span class="keyword">static</span> DBUtilsDao dao = <span class="keyword">new</span> DBUtilsDao();</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">testInsert</span><span class="params">()</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		User user = <span class="keyword">new</span> User();</span><br><span class="line">		user.setName(<span class="string">&quot;zhaoliu&quot;</span>);</span><br><span class="line">		user.setPassword(<span class="string">&quot;666666&quot;</span>);</span><br><span class="line">		<span class="keyword">boolean</span> b = dao.insert(user);</span><br><span class="line">		System.out.println(b);</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		testInsert();</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//更新数据</span></span><br><span class="line"><span class="keyword">package</span> DBUtils_Test;</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">import</span> DBUtils_Dao.DBUtilsDao;</span><br><span class="line"><span class="keyword">import</span> UserBean.User;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">Update_test</span> </span>&#123;</span><br><span class="line">	<span class="keyword">private</span> <span class="keyword">static</span> DBUtilsDao dao = <span class="keyword">new</span> DBUtilsDao();</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">testupdate</span><span class="params">()</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		User user = <span class="keyword">new</span> User();</span><br><span class="line">		user.setName(<span class="string">&quot;zhaoliu&quot;</span>);</span><br><span class="line">		user.setPassword(<span class="string">&quot;666777&quot;</span>);</span><br><span class="line">		user.setId(<span class="number">2</span>);</span><br><span class="line">		<span class="keyword">boolean</span> b = dao.update(user);</span><br><span class="line">		System.out.println(b);</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		testupdate();</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//根据ID删除</span></span><br><span class="line"><span class="keyword">package</span> DBUtils_Test;</span><br><span class="line"><span class="keyword">import</span> DBUtils_Dao.DBUtilsDao;</span><br><span class="line"><span class="keyword">import</span> UserBean.User;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">Delete_Test</span> </span>&#123;</span><br><span class="line">	<span class="keyword">private</span> <span class="keyword">static</span> DBUtilsDao dao = <span class="keyword">new</span> DBUtilsDao();</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">testdelete</span><span class="params">()</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		<span class="keyword">boolean</span> b = dao.delete(<span class="number">2</span>);</span><br><span class="line">		System.out.println(b);</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		testdelete();</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">//根据ID查询</span></span><br><span class="line"><span class="keyword">package</span> DBUtils_Test;</span><br><span class="line"><span class="keyword">import</span> java.sql.SQLException;</span><br><span class="line"><span class="keyword">import</span> DBUtils_Dao.DBUtilsDao;</span><br><span class="line"><span class="keyword">import</span> UserBean.User;</span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">Query_Test</span> </span>&#123;</span><br><span class="line">	<span class="keyword">private</span> <span class="keyword">static</span> DBUtilsDao dao = <span class="keyword">new</span> DBUtilsDao();</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">testfind</span><span class="params">()</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		User user = dao.find(<span class="number">3</span>);</span><br><span class="line">		System.out.println(user.getId() + <span class="string">&quot;,&quot;</span> + user.getName() + <span class="string">&quot;,&quot;</span></span><br><span class="line">				+ user.getPassword());</span><br><span class="line">	&#125;</span><br><span class="line">	<span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> <span class="keyword">throws</span> SQLException </span>&#123;</span><br><span class="line">		testfind();</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">小李博客</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://xiaoliblog.cn">https://xiaoliblog.cn</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="http://xiaoliblog.cn" target="_blank">小李博客</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/JavaWeb/">JavaWeb</a></div><div class="post_share"><div class="social-share" data-image="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><div class="post-reward"><div class="reward-button button--animated"><i class="fas fa-qrcode"></i> 打赏</div><div class="reward-main"><ul class="reward-all"><li class="reward-item"><a href="/img/wechat.png" target="_blank"><img class="post-qr-code-img" src="/img/wechat.png" alt="微信"/></a><div class="post-qr-code-desc">微信</div></li><li class="reward-item"><a href="/img/alipay.png" target="_blank"><img class="post-qr-code-img" src="/img/alipay.png" alt="支付宝"/></a><div class="post-qr-code-desc">支付宝</div></li></ul></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/page/JavaWeb10.html"><img class="prev-cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" onerror="onerror=null;src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">JavaWeb🌍Ajax异步检测</div></div></a></div><div class="next-post pull-right"><a href="/page/JavaWeb08.html"><img class="next-cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" onerror="onerror=null;src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">JavaWeb🌍过滤/监听</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span> 相关推荐</span></div><div class="relatedPosts-list"><div><a href="/page/JavaWeb10.html" title="JavaWeb🌍Ajax异步检测"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-15</div><div class="title">JavaWeb🌍Ajax异步检测</div></div></a></div><div><a href="/page/JavaWeb11.html" title="JavaWeb🌍Frame框架"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-15</div><div class="title">JavaWeb🌍Frame框架</div></div></a></div><div><a href="/page/JavaWeb06.html" title="JavaWeb🌍EL表达式"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-15</div><div class="title">JavaWeb🌍EL表达式</div></div></a></div><div><a href="/page/JavaWeb07.html" title="JavaWeb🌍JSTL标签"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-15</div><div class="title">JavaWeb🌍JSTL标签</div></div></a></div><div><a href="/page/JavaWeb18.html" title="JavaWeb🌍smbms注销及权限过滤"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-18</div><div class="title">JavaWeb🌍smbms注销及权限过滤</div></div></a></div><div><a href="/page/JavaWeb16.html" title="JavaWeb🌍smbms项目搭建"><img class="cover" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@41021477288a29257324f7413b81c299b3dec54e/2021/03/21/3d05e9b134d98fc70cebf887c1359f71.png" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2021-01-16</div><div class="title">JavaWeb🌍smbms项目搭建</div></div></a></div></div></div><hr/><div id="post-comment"><div class="comment-head"><div class="comment-headline"><i class="fas fa-comments fa-fw"></i><span> 评论</span></div></div><div class="comment-wrap"><div><div id="twikoo-wrap"></div></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="card-info-avatar is-center"><img class="avatar-img" src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@6b5e7ef72be1c8973d94e5a9c49accbf775ad820/2021/02/01/c485da031fe0e464d04eaba8a66c4a8f.png" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/><div class="author-info__name">小李博客</div><div class="author-info__description">越努力，越幸运！</div></div><div class="card-info-data"><div class="card-info-data-item is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">210</div></a></div><div class="card-info-data-item is-center"><a href="/tags/"><div class="headline">标签</div><div class="length-num">38</div></a></div><div class="card-info-data-item is-center"><a href="/categories/"><div class="headline">分类</div><div class="length-num">56</div></a></div></div><a class="button--animated" id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xiaoliblog"><i class="fab fa-github"></i><span>博主的GitHub首页</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://gitee.com/xiaoliblog" target="_blank" title="Gitee"><i class="iconfont icon-gitee card_icon_gitee"></i></a><a class="social-icon" href="https://space.bilibili.com/390969485" target="_blank" title="BiliBili"><i class="iconfont icon-bilibili card_icon_bilibili"></i></a><a class="social-icon" href="http://wpa.qq.com/msgrd?v=3&amp;uin=2312057536&amp;site=CSDN&amp;menu=yes" target="_blank" title="QQ"><i class="iconfont icon-qq card_icon_qq"></i></a><a class="social-icon" href="https://github.com/xiaoliblog" target="_blank" title="GitHub"><i class="iconfont icon-git card_icon_git"></i></a><a class="social-icon" href="https://blog.csdn.net/qq_43266250?spm=1010.2135.3001.5113" target="_blank" title="CSDN"><i class="iconfont icon-csdn card_icon_csdn"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn card-announcement-animation"></i><span>公告</span></div><div class="announcement_content">正在考研备考中💦</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#JDBC%E7%AE%80%E4%BB%8B"><span class="toc-number">1.</span> <span class="toc-text">JDBC简介</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E5%B8%B8%E7%94%A8SQL%E8%AF%AD%E5%8F%A5"><span class="toc-number">2.</span> <span class="toc-text">常用SQL语句</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%88%9B%E5%BB%BA%E8%A1%A8%E5%92%8C%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">2.1.</span> <span class="toc-text">创建表和数据库</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#CRUD"><span class="toc-number">2.2.</span> <span class="toc-text">CRUD</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E6%9D%A1%E4%BB%B6"><span class="toc-number">2.3.</span> <span class="toc-text">查询条件</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#JDBC%E5%B8%B8%E7%94%A8API"><span class="toc-number">3.</span> <span class="toc-text">JDBC常用API</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%AE%9E%E7%8E%B0JDBC%E7%A8%8B%E5%BA%8F"><span class="toc-number">3.1.</span> <span class="toc-text">实现JDBC程序</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#PreparedStatement%E5%AF%B9%E8%B1%A1"><span class="toc-number">3.2.</span> <span class="toc-text">PreparedStatement对象</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#ResultSet%E5%AF%B9%E8%B1%A1"><span class="toc-number">3.3.</span> <span class="toc-text">ResultSet对象</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#JDBC%E4%BA%8B%E5%8A%A1"><span class="toc-number">4.</span> <span class="toc-text">JDBC事务</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E4%BA%8B%E5%8A%A1%E6%A6%82%E8%BF%B0"><span class="toc-number">4.1.</span> <span class="toc-text">事务概述</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%B5%8B%E8%AF%95%E4%BA%8B%E5%8A%A1"><span class="toc-number">4.2.</span> <span class="toc-text">测试事务</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0"><span class="toc-number">5.</span> <span class="toc-text">数据库连接池</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#DataSource%E6%8E%A5%E5%8F%A3"><span class="toc-number">5.1.</span> <span class="toc-text">DataSource接口</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#DBCP%E6%95%B0%E6%8D%AE%E6%BA%90"><span class="toc-number">5.2.</span> <span class="toc-text">DBCP数据源</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#C3P0%E6%95%B0%E6%8D%AE%E6%BA%90"><span class="toc-number">5.3.</span> <span class="toc-text">C3P0数据源</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link" href="#DBUtils%E5%B7%A5%E5%85%B7"><span class="toc-number">6.</span> <span class="toc-text">DBUtils工具</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#QueryRunner%E7%B1%BB"><span class="toc-number">6.1.</span> <span class="toc-text">QueryRunner类</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#ResultSetHandler%E6%8E%A5%E5%8F%A3"><span class="toc-number">6.2.</span> <span class="toc-text">ResultSetHandler接口</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#ResultSetHandler%E5%AE%9E%E7%8E%B0%E7%B1%BB"><span class="toc-number">6.3.</span> <span class="toc-text">ResultSetHandler实现类</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#DBUtils%E5%AE%9E%E7%8E%B0CURD"><span class="toc-number">6.4.</span> <span class="toc-text">DBUtils实现CURD</span></a></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/page/project01.html" title="前端实例🥳响应式网站首页"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@c19917500ab083c77c7613263ba7ee74d5a08ae6/2021/04/30/469f30b141d73fa0fc4c962662d5813f.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="前端实例🥳响应式网站首页"/></a><div class="content"><a class="title" href="/page/project01.html" title="前端实例🥳响应式网站首页">前端实例🥳响应式网站首页</a><time datetime="2021-04-30T11:50:53.094Z" title="发表于 2021-04-30 19:50:53">2021-04-30</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/WeChatpay.html" title="微信支付对接"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@f2f670b92ea149650ffa7834354fc90284f2f44a/2021/04/29/5bdc9b381a06193d27cf2fb7c2fb608a.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="微信支付对接"/></a><div class="content"><a class="title" href="/page/WeChatpay.html" title="微信支付对接">微信支付对接</a><time datetime="2021-04-29T12:20:48.070Z" title="发表于 2021-04-29 20:20:48">2021-04-29</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Alipay.html" title="支付宝支付对接"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61a9b6a6e09e4bda38bb08e3104b717885beaee5/2021/04/29/c3fa51f9cf14e90d9e5a7aa8814dd041.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="支付宝支付对接"/></a><div class="content"><a class="title" href="/page/Alipay.html" title="支付宝支付对接">支付宝支付对接</a><time datetime="2021-04-27T16:00:00.000Z" title="发表于 2021-04-28 00:00:00">2021-04-28</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Vuejs11.html" title="Vue.js前端框架🎯Pagination+PageHelper实现分页"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61bf4245f6d84d2d0f66d163b89c916788cc1845/2021/04/13/ec6b232f8fe5a840e4bd8c3eabcf49b2.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="Vue.js前端框架🎯Pagination+PageHelper实现分页"/></a><div class="content"><a class="title" href="/page/Vuejs11.html" title="Vue.js前端框架🎯Pagination+PageHelper实现分页">Vue.js前端框架🎯Pagination+PageHelper实现分页</a><time datetime="2021-04-26T14:48:39.701Z" title="发表于 2021-04-26 22:48:39">2021-04-26</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/page/Vuejs10.html" title="Vue.js前端框架🎯Vue-Element-admin模版"><img src="https://cdn.jsdelivr.net/gh/xiaoliblog/image@61bf4245f6d84d2d0f66d163b89c916788cc1845/2021/04/13/ec6b232f8fe5a840e4bd8c3eabcf49b2.png" onerror="this.onerror=null;this.src='https://cdn.jsdelivr.net/gh/lzyblog/image@main/2020/11/19/bd16b394f7359083b1f6072a67e3f968.png'" alt="Vue.js前端框架🎯Vue-Element-admin模版"/></a><div class="content"><a class="title" href="/page/Vuejs10.html" title="Vue.js前端框架🎯Vue-Element-admin模版">Vue.js前端框架🎯Vue-Element-admin模版</a><time datetime="2021-04-19T16:00:00.000Z" title="发表于 2021-04-20 00:00:00">2021-04-20</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2021 By 小李博客</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">🎯Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">🎉Butterfly</a></div><div class="footer_custom_text"><span><a style="margin-inline:5px" target="_blank" href="https://hexo.io/"><img src="https://img.shields.io/badge/Frame-Hexo-blue?style=flat&logo=hexo" title="博客框架为Hexo"></a><a style="margin-inline:5px" target="_blank" href="https://butterfly.js.org/"><img src="https://img.shields.io/badge/Theme-Butterfly-6513df?style=flat&logo=bitdefender" title="主题采用butterfly"></a><a style="margin-inline:5px" target="_blank" href="https://www.jsdelivr.com/"><img src="https://img.shields.io/badge/CDN-jsDelivr-orange?style=flat&logo=jsDelivr" title="本站使用JsDelivr为静态资源提供CDN加速"></a><a style="margin-inline:5px" target="_blank" href="https://vercel.com/ "><img src="https://img.shields.io/badge/Hosted-Vercel-brightgreen?style=flat&logo=Vercel" title="本站采用双线部署，默认线路托管于Vercel"></a><a style="margin-inline:5px" target="_blank" href="https://coding.net/ "><img src="https://img.shields.io/badge/Hosted-Coding-0cedbe?style=flat&logo=Codio" title="本站采用双线部署，联通线路托管于Coding"></a><a style="margin-inline:5px" target="_blank" href="https://github.com/"><img src="https://img.shields.io/badge/Source-Github-d021d6?style=flat&logo=GitHub" title="本站项目由Gtihub托管"></a><a style="margin-inline:5px" target="_blank" href="http://creativecommons.org/licenses/by-nc-sa/4.0/"><img src="https://img.shields.io/badge/Copyright-BY--NC--SA%204.0-d42328?style=flat&logo=Claris" title="本站采用知识共享署名-非商业性使用-相同方式共享4.0国际许可协议进行许可"></a></span></div><div class="icp"><a target="_blank" rel="noopener" href="https://beian.miit.gov.cn/"><img class="icp-icon" src="/img/icp.png" alt="ICP"/><span>湘ICP备2021002541号</span></a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="font-plus" type="button" title="放大字体"><i class="fas fa-plus"></i></button><button id="font-minus" type="button" title="缩小字体"><i class="fas fa-minus"></i></button><button id="translateLink" type="button" title="简繁转换">繁</button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="chat_btn" type="button" title="rightside.chat_btn"><i class="fas fa-sms"></i></button><a id="to_comment" href="#post-comment" title="直达评论"><i class="fas fa-comments"></i></a><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div id="algolia-search"><div class="search-dialog"><div class="search-dialog__title" id="algolia-search-title">Algolia</div><div id="algolia-input-panel"><div id="algolia-search-input"></div></div><hr/><div id="algolia-search-results"><div id="algolia-hits"></div><div id="algolia-pagination"></div><div id="algolia-stats"></div></div><span class="search-close-button"><i class="fas fa-times"></i></span></div><div id="search-mask"></div></div><div><script src="/js/utils.js"></script><script src="https://cdn.jsdelivr.net/npm/vue@2.6.11"></script><script src="/js/main.js"></script><script defer src="/js/tw_cn.js"></script><script defer src="https://cdn.jsdelivr.net/npm/medium-zoom/dist/medium-zoom.min.js"></script><script src="https://cdn.jsdelivr.net/npm/instant.page/instantpage.min.js" type="module" defer></script><script defer src="https://cdn.jsdelivr.net/npm/node-snackbar/dist/snackbar.min.js"></script><script defer src="/js/search/algolia.js"></script><div class="js-pjax"><script>if (!window.MathJax) {
  window.MathJax = {
    loader: {
      source: {
        '[tex]/amsCd': '[tex]/amscd'
      }
    },
    tex: {
      inlineMath: [ ['$','$'], ["\\(","\\)"]],
      tags: 'ams'
    },
    options: {
      renderActions: {
        findScript: [10, doc => {
          for (const node of document.querySelectorAll('script[type^="math/tex"]')) {
            const display = !!node.type.match(/; *mode=display/)
            const math = new doc.options.MathItem(node.textContent, doc.inputJax[0], display)
            const text = document.createTextNode('')
            node.parentNode.replaceChild(text, node)
            math.start = {node: text, delim: '', n: 0}
            math.end = {node: text, delim: '', n: 0}
            doc.math.push(math)
          }
        }, ''],
        addClass: [200,() => {
          document.querySelectorAll('mjx-container:not([display=\'true\']').forEach( node => {
            const target = node.parentNode
            if (!target.classList.contains('has-jax')) {
              target.classList.add('mathjax-overflow')
            }
          })
        }, '', false]
      }
    }
  }
  
  const script = document.createElement('script')
  script.src = 'https://cdn.jsdelivr.net/npm/mathjax@3/es5/tex-mml-chtml.js'
  script.id = 'MathJax-script'
  script.async = true
  document.head.appendChild(script)
} else {
  MathJax.startup.document.state(0)
  MathJax.texReset()
  MathJax.typeset()
}</script><script>if (document.getElementsByClassName('mermaid').length) {
  if (window.mermaidJsLoad) mermaid.init()
  else {
    getScript('https://cdn.jsdelivr.net/npm/mermaid/dist/mermaid.min.js').then(() => {
      window.mermaidJsLoad = true
      mermaid.initialize({
        theme: 'default',
      })
      false && mermaid.init()
    })
  }
}</script><script>(()=>{
  const $countDom = document.getElementById('twikoo-count')
  const init = () => {
    twikoo.init(Object.assign({
      el: '#twikoo-wrap',
      envId: 'xiaoliblog-8gj3j5045d5b0896',
      region: ''
    }, null))
  }

  const getCount = () => {
    twikoo.getCommentsCount({
      envId: 'xiaoliblog-8gj3j5045d5b0896',
      region: '',
      urls: [window.location.pathname],
      includeReply: false
    }).then(function (res) {
      $countDom.innerText = res[0].count
    }).catch(function (err) {
      console.error(err);
    });
  }

  const loadTwikoo = (bool = false) => {
    if (typeof twikoo === 'object') {
      init()
      bool && $countDom && setTimeout(getCount,0)
    } else {
      getScript('https://cdn.jsdelivr.net/npm/twikoo@1.3.0/dist/twikoo.all.min.js').then(()=> {
        init()
        bool && $countDom && setTimeout(getCount,0)
      })
    }
  }

  if ('Twikoo' === 'Twikoo' || !true) {
    if (true) btf.loadComment(document.getElementById('twikoo-wrap'), loadTwikoo)
    else loadTwikoo(true)
  } else {
    window.loadOtherComment = () => {
      loadTwikoo()
    }
  }
})()</script></div><script defer src="//lib.baomitu.com/jquery/3.5.1/jquery.min.js"></script><script defer src="https://myhkw.cn/api/player/160561664166" id="myhk" key="160561664166" m="1"></script><div><canvas id="snow" style="position:fixed;top:0;left:0;width:100%;height:100%;z-index:99999;pointer-events:none"></canvas></div><script>const notMobile = (!(navigator.userAgent.match(/(phone|pad|pod|iPhone|iPod|ios|iPad|Android|Mobile|BlackBerry|IEMobile|MQQBrowser|JUC|Fennec|wOSBrowser|BrowserNG|WebOS|Symbian|Windows Phone)/i)));</script><scrip async type="text/javascript" src="https://cdn.jsdelivr.net/gh/Candinya/Kratos-Rebirth@latest/source/js/snow.min.js"></scrip><scrip defer src="https://cdn.jsdelivr.net/npm/hexo-theme-volantis@latest/source/js/issues.min.js"></scrip><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><script>var gitcalendar = new Vue({
  el: '#gitcalendar',
  data: {
    simplemode: true, 
    user: 'xiaoliblog',
    fixed: 'fixed',
    px: 'px',
    x: '',
    y: '',
    span1: '',
    span2: '',
    month: ['一月', '二月', '三月', '四月', '五月', '六月', '七月', '八月', '九月', '十月', '十一月', '十二月'],
    monthchange: [],
    oneyearbeforeday: '',
    thisday: '',
    amonthago: '',
    aweekago: '',
    weekdatacore: 0,
    datacore: 0,
    total: 0,
    datadate: '',
    data: [],
    positionplusdata: [],
    firstweek: [],
    lastweek: [],
    beforeweek: [],
    thisweekdatacore: 0,
    mounthbeforeday: 0,
    mounthfirstindex: 0,
    crispedges: 'crispedges',
    thisdayindex: 0,
    amonthagoindex: 0,
    amonthagoweek: [],
    firstdate: [],
    first2date: [],
    montharrbefore: [],
    monthindex: 0,
    color: ['#ebedf0', '#f1f8ff', '#dbedff', '#c8e1ff', '#79b8ff', '#2188ff', '#0366d6', '#005cc5', '#044289', '#032f62', '#05264c']
  },
  methods: {
    selectStyle(data, event) {
      document.querySelector('.angle-wrapper').style.display = 'block'
      this.span1 = data.date;
      this.span2 = data.count;
      this.x = event.clientX - 100;
      this.y = event.clientY - 60
    },
    outStyle() {
      document.querySelector('.angle-wrapper').style.display = 'none'
    },
    thiscolor(x) {
      if (x === 0) {
        let i = parseInt(x / 2);
        return this.color[0]
      } else if (x < 2) {
        return this.color[1]
      } else if (x < 20) {
        let i = parseInt(x / 2);
        return this.color[i]
      } else {
        return this.color[9]
      }
    },
  }
});
var apiurl = 'python-github-calendar-api-ruby.vercel.app' ? 'https://python-github-calendar-api-ruby.vercel.app/api?' : 'https://githubapi.ryanchristian.dev/user/'
var githubapiurl = apiurl + gitcalendar.user;
//canvas绘图
function responsiveChart() {
  let c = document.getElementById("gitcanvas");
  if (c) {
    let cmessage = document.getElementById("gitmessage");
    let ctx = c.getContext("2d");
    c.width = document.getElementById("gitcalendarcanvasbox").offsetWidth;
    let linemaxwitdh = 0.96 * c.width / gitcalendar.data.length;
    c.height = 9 * linemaxwitdh;
    let lineminwitdh = 0.8 * linemaxwitdh;
    let setposition = {
      x: 0.02 * c.width,
      y: 0.025 * c.width
    };
    for (let week in gitcalendar.data) {
      weekdata = gitcalendar.data[week];
      for (let day in weekdata) {
        let dataitem = {
          date: "",
          count: "",
          x: 0,
          y: 0
        };
        gitcalendar.positionplusdata.push(dataitem);
        ctx.fillStyle = gitcalendar.thiscolor(weekdata[day].count);
        setposition.y = Math.round(setposition.y * 100) / 100;
        dataitem.date = weekdata[day].date;
        dataitem.count = weekdata[day].count;
        dataitem.x = setposition.x;
        dataitem.y = setposition.y;
        ctx.fillRect(setposition.x, setposition.y, lineminwitdh, lineminwitdh);
        setposition.y = setposition.y + linemaxwitdh
      };
      setposition.y = 0.025 * c.width;
      setposition.x = setposition.x + linemaxwitdh
    };
    ctx.font = "600  Arial";
    ctx.fillStyle = '#aaa';
    ctx.fillText("日", 0, 1.9 * linemaxwitdh);
    ctx.fillText("二", 0, 3.9 * linemaxwitdh);
    ctx.fillText("四", 0, 5.9 * linemaxwitdh);
    ctx.fillText("六", 0, 7.9 * linemaxwitdh);
    let monthindexlist = c.width / 24;
    for (let index in gitcalendar.monthchange) {
      ctx.fillText(gitcalendar.monthchange[index], monthindexlist, 0.7 * linemaxwitdh);
      monthindexlist = monthindexlist + c.width / 12
    };
    cmessage.onmousemove = function(event) {
      document.querySelector('.angle-wrapper').style.display = 'none'
    };
    c.onmousemove = function(event) {
      document.querySelector('.angle-wrapper').style.display = 'none'
      getMousePos(c, event);
    };

    function getMousePos(canvas, event) {
      var rect = canvas.getBoundingClientRect();
      var x = event.clientX - rect.left * (canvas.width / rect.width);
      var y = event.clientY - rect.top * (canvas.height / rect.height);
      //console.log("x:"+x+",y:"+y);
      for (let item of gitcalendar.positionplusdata) {
        let lenthx = x - item.x;
        let lenthy = y - item.y;
        //console.log(lenthx,lenthy);
        if (0 < lenthx && lenthx < lineminwitdh) {
          if (0 < lenthy && lenthy < lineminwitdh) {
            //console.log(item.date,item.count)
            document.querySelector('.angle-wrapper').style.display = 'block'
            gitcalendar.span1 = item.date;
            gitcalendar.span2 = item.count;
            gitcalendar.x = event.clientX - 100;
            gitcalendar.y = event.clientY - 60
          }
        }
        //if(0< x - item.x <lineminwitdh&&0< y - item.y <lineminwitdh){
        //console.log(item.count,item.date);
        //}
      }
    }
  }
}
//数据统计算法
function addlastmonth() {
  if (gitcalendar.thisdayindex === 0) {
    thisweekcore(52);
    thisweekcore(51);
    thisweekcore(50);
    thisweekcore(49);
    thisweekcore(48);
    gitcalendar.thisweekdatacore += gitcalendar.firstdate[6].count;
    gitcalendar.amonthago = gitcalendar.firstdate[6].date
  } else {
    thisweekcore(52);
    thisweekcore(51);
    thisweekcore(50);
    thisweekcore(49);
    thisweek2core();
    gitcalendar.amonthago = gitcalendar.first2date[gitcalendar.thisdayindex - 1].date
  }
};

function thisweek2core() {
  for (let i = gitcalendar.thisdayindex - 1; i < gitcalendar.first2date.length; i++) {
    gitcalendar.thisweekdatacore += gitcalendar.first2date[i].count
  }
};

function thisweekcore(index) {
  for (let item of gitcalendar.data[index]) {
    gitcalendar.thisweekdatacore += item.count
  }
};

function addlastweek() {
  for (let item of gitcalendar.lastweek) {
    gitcalendar.weekdatacore += item.count
  }
};

function addbeforeweek() {
  for (let i = gitcalendar.thisdayindex; i < gitcalendar.beforeweek.length; i++) {
    gitcalendar.weekdatacore += gitcalendar.beforeweek[i].count
  }
};

function addweek(data) {
  if (gitcalendar.thisdayindex === 6) {
    gitcalendar.aweekago = gitcalendar.lastweek[0].date;
    addlastweek()
  } else {
    lastweek = data.contributions[51];
    gitcalendar.aweekago = lastweek[gitcalendar.thisdayindex + 1].date;
    addlastweek();
    addbeforeweek()
  }
}

fetch(githubapiurl)
  .then(data => data.json())
  .then(data => {
    gitcalendar.data = data.contributions;
    gitcalendar.total = data.total;
    gitcalendar.first2date = gitcalendar.data[48];
    gitcalendar.firstdate = gitcalendar.data[47];
    gitcalendar.firstweek = data.contributions[0];
    gitcalendar.lastweek = data.contributions[52];
    gitcalendar.beforeweek = data.contributions[51];
    gitcalendar.thisdayindex = gitcalendar.lastweek.length - 1;
    gitcalendar.thisday = gitcalendar.lastweek[gitcalendar.thisdayindex].date;
    gitcalendar.oneyearbeforeday = gitcalendar.firstweek[0].date;
    gitcalendar.monthindex = gitcalendar.thisday.substring(5, 7) * 1;
    gitcalendar.montharrbefore = gitcalendar.month.splice(gitcalendar.monthindex, 12 - gitcalendar.monthindex);
    gitcalendar.monthchange = gitcalendar.montharrbefore.concat(gitcalendar.month);
    addweek(data);
    addlastmonth();
    responsiveChart();
  })
  .catch(function(error) {
    console.log(error);
  });

//手机版更换为svg绘制
if (document.getElementById("gitcalendarcanvasbox").offsetWidth < 500) {
  gitcalendar.simplemode = false
}

//当改变窗口大小时重新绘制canvas
window.onresize = function() {
  if (gitcalendar.simplemode) responsiveChart()
}

//解决滚动滑轮时出现的标签显示
window.onscroll = function() {
  if (document.querySelector('.angle-wrapper')) {
    document.querySelector('.angle-wrapper').style.display = 'none'
  }
};</script></div><script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({"pluginRootPath":"live2dw/","pluginJsPath":"lib/","pluginModelPath":"assets/","tagMode":false,"log":false,"model":{"jsonPath":"/live2dw/assets/hijiki.model.json"},"display":{"position":"right","width":150,"height":300},"mobile":{"show":true},"react":{"opacity":0.7}});</script></body></html>